package poi.excel;

import cn.hutool.core.lang.Assert;
import org.apache.poi.hssf.usermodel.HSSFAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFAnchor;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigInteger;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.UUID;
import java.util.regex.Pattern;

public class ReadExcel {

    /**
     * 读取 Excel 表格
     *
     * @param path       路径
     * @param sheetIndex sheet索引
     * @param rowCount   读取的行数
     * @return
     */
    public static List<LinkedHashMap<Integer, String>> readExcel(String path, Integer sheetIndex, Integer rowCount) throws IOException {
        Assert.notBlank(path);
        sheetIndex = sheetIndex == null ? 0 : sheetIndex;

        File file = new File(path.replaceAll("&amp;", "&"));

        Workbook workBook = null;
        try {
            workBook = WorkbookFactory.create(file);
            Sheet sheet = workBook.getSheetAt(sheetIndex);
            Integer endRowCount = rowCount == null ? (sheet.getLastRowNum() + 1) : rowCount;
            Integer startRowNum = sheet.getFirstRowNum();// 需要读取的起始行

            List<LinkedHashMap<Integer, String>> list = new ArrayList<>(endRowCount - startRowNum);
            LinkedHashMap<Integer, String> linkedHashMap = null;
            Row row = null;
            Short cellCount = null;
            Short cellNum = null;
            for (int i = startRowNum; i < rowCount; i++) {
                linkedHashMap = new LinkedHashMap<>();
                row = sheet.getRow(i);
                if (row == null) break;

                if (row.getFirstCellNum() >= 0 && row.getLastCellNum() > 0) {
                    cellNum = row.getFirstCellNum();
                    cellCount = row.getLastCellNum();

                    for (int j = cellNum; j < cellCount; j++) {
                        Cell cell = row.getCell(j);
                        linkedHashMap.put(j, getCellValue(cell));
                    }
                    list.add(linkedHashMap);
                }
            }
            return list;
        } catch (Exception e) {
            throw e;
        } finally {
            if (workBook != null) {
                try {
                    workBook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 获取单元格值
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        if ((cell == null)
                || ((cell.getCellType() == CellType.STRING) && (cell.getStringCellValue() == null || "".equals(cell.getStringCellValue().trim())))) {
            return null;
        }
        CellType cellType = cell.getCellType();
        if (cellType == CellType.BLANK) {
            return null;
        } else if (cellType == CellType.BOOLEAN) {
            return cell.getBooleanCellValue() ? "1" : "0";
        } else if (cellType == CellType.ERROR) {
            // return cell.getErrorCellValue();
            return "";
        } else if (cellType == CellType.FORMULA) {
            try {
                return getValueOfNumericCell(cell);
            } catch (IllegalStateException e) {
                return cell.getRichStringCellValue().toString();
            }
        } else if (cellType == CellType.NUMERIC) {
            return getValueOfNumericCell(cell);
        } else if (cellType == CellType.STRING) {
            return getValueOfStringCell(cell);
        } else {
            return null;
        }
    }

    private static String getValueOfStringCell(Cell cell) {
        String str = cell.getStringCellValue();
        if ("-".equals(str)) {
            str = "";
        }
        return str;
    }

    private static String getValueOfNumericCell(Cell cell) {
        Boolean isDate = DateUtil.isCellDateFormatted(cell);
        Double d = cell.getNumericCellValue();
        String o = null;
        if (isDate) {
            o = DateFormat.getDateTimeInstance().format(cell.getDateCellValue());
        } else {
            o = getRealStringValueOfDouble(d);
        }
        return o;
    }

    // 处理科学计数法与普通计数法的字符串显示，尽最大努力保持精度
    private static String getRealStringValueOfDouble(Double d) {
        String doubleStr = d.toString();
        boolean b = doubleStr.contains("E");
        int indexOfPoint = doubleStr.indexOf('.');
        if (b) {
            int indexOfE = doubleStr.indexOf('E');
            // 小数部分
            BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint + BigInteger.ONE.intValue(), indexOfE));
            // 指数
            int pow = Integer.valueOf(doubleStr.substring(indexOfE + BigInteger.ONE.intValue()));
            int xsLen = xs.toByteArray().length;
            int scale = (xsLen - pow) > 0 ? xsLen - pow : 0;
            doubleStr = String.format("%." + scale + "f", d);
        } else {
            java.util.regex.Pattern p = Pattern.compile(".0$");
            java.util.regex.Matcher m = p.matcher(doubleStr);
            if (m.find()) {
                doubleStr = doubleStr.replace(".0", "");
            }
        }
        return doubleStr;
    }

    /**
     * 读取 Excel 中的图片
     *
     * @param path       路径
     * @param savePath   图片保存路径
     * @param sheetIndex sheet索引
     * @param rowCount   读取的行数
     * @return
     */
    public static LinkedHashMap<String, String> readExcelPic(String path, String savePath, Integer sheetIndex, Integer rowCount) throws IOException {
        Assert.notBlank(path);
        sheetIndex = sheetIndex == null ? 0 : sheetIndex;

        File saveFolder = new File(savePath);
        if (!saveFolder.exists() && !saveFolder.isDirectory()) {
            saveFolder.mkdirs();
        }
        File file = new File(path.replaceAll("&amp;", "&"));
        Workbook workBook = null;

        try {
            workBook = WorkbookFactory.create(file);
            List<? extends PictureData> pictures = workBook.getAllPictures();
            Sheet sheet = workBook.getSheetAt(sheetIndex);
            LinkedHashMap<String, PictureData> linkedHashMap = new LinkedHashMap<>();
            Drawing<?> drawing = sheet.getDrawingPatriarch();

            int i = 0;
            for (Shape shape : drawing) {
                if (rowCount != null && i >= rowCount) {
                    break;
                }
                ChildAnchor anchor = shape.getAnchor();
                if (anchor instanceof HSSFAnchor) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData pictureData = (HSSFPictureData) pictures.get(pictureIndex);
                    String picIndex = sheetIndex + "_" + anchor.getDx1() + "_" + anchor.getDy1();
                    linkedHashMap.put(picIndex, pictureData);
                } else if (anchor instanceof XSSFAnchor) {
                    XSSFPicture pic = (XSSFPicture) shape;
                    XSSFPictureData pictureData = pic.getPictureData();
                    XSSFClientAnchor XSSFAnchor = pic.getPreferredSize();
                    CTMarker ctMarker = XSSFAnchor.getFrom();
                    String picIndex = sheetIndex + "_" + ctMarker.getRow() + "_" + ctMarker.getCol();
                    linkedHashMap.put(picIndex, pictureData);
                }
                i++;
            }

            LinkedHashMap<String, String> returnMap = new LinkedHashMap<>(linkedHashMap.size());
            if (!linkedHashMap.isEmpty()) {
                for (String key : linkedHashMap.keySet()) {
                    PictureData value = linkedHashMap.get(key);
                    String ext = null;
                    switch (value.getPictureType()) {
                        case Workbook.PICTURE_TYPE_EMF:
                            ext = ".emf";
                            break;
                        case Workbook.PICTURE_TYPE_WMF:
                            ext = ".wmf";
                            break;
                        case Workbook.PICTURE_TYPE_PICT:
                            ext = ".pict";
                            break;
                        case Workbook.PICTURE_TYPE_JPEG:
                            ext = ".jpeg";
                            break;
                        case Workbook.PICTURE_TYPE_PNG:
                            ext = ".png";
                            break;
                        case Workbook.PICTURE_TYPE_DIB:
                            ext = ".dib";
                            break;
                        default:
                            break;
                    }
                    if(ext != null){
                        byte[] data = value.getData();
                        String pic_name = UUID.randomUUID() + ext;
                        FileOutputStream out = new FileOutputStream(savePath + pic_name);
                        out.write(data);
                        out.close();
                        returnMap.put(key, pic_name);
                    }
                }
            }
            return returnMap;
        } catch (IOException e) {
            throw e;
        } finally {
            if (workBook != null) {
                try {
                    workBook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
